package cn.su.dao.impl;

import cn.su.dao.billDao;
import cn.su.pojo.bill;
import cn.su.pojo.provider;
import cn.su.utils.BaseDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import java.sql.SQLException;

import java.util.Date;
import java.util.List;

public class billDaoimpl implements billDao {
    @Override
    public List<bill> billList() {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String sql="select a.id,a.billCode,a.productName,a.productDesc ,a.productUnit,a.productCount,a.totalPrice,a.isPayment,a.createdBy,a.creationDate,a.modifyBy,a.modifyDate,a.providerId,b.proName from smbms_bill as a  left join smbms_provider as b "+
                "on  a.providerId=b.id";

        try {
            List<bill>  billList = queryRunner.query(sql, new BeanListHandler<bill>(bill.class));
            return billList;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<provider> providerandbill_List() {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String sql="select DISTINCT * from smbms_bill as a,smbms_provider as b where a.providerId=b.id  ";
        List<provider> providerList = null;
        try {
            providerList = queryRunner.query(sql, new BeanListHandler<provider>(provider.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return providerList;
    }

    @Override
    public List<bill> billSearch(String ProductName,String ProviderName,String IsPayment) {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        List<bill> bills=null;
        StringBuffer sql=new StringBuffer("select * ,b.proName from smbms_bill as a  left join smbms_provider as b "+
                "on  a.providerId=b.id  where 1=1 ");
        if (ProductName!=null&& !ProductName.trim().equals("")){
            sql.append(" and a.ProductName Like '%"+ProductName+"%'");  //注意sql字符串’‘问题
        }
        if (ProviderName!=null&& !ProviderName.trim().equals("")&&!ProviderName.equals("0")){
            sql.append(" and b.proName = '"+ProviderName+"'");
        }
        if (IsPayment!=null && !IsPayment.trim().equals("") && !IsPayment.equals("0")){
            sql.append(" and a.IsPayment = "+IsPayment);
        }

        try {
          bills = queryRunner.query(sql.toString(), new BeanListHandler<bill>(bill.class));

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return bills;
    }

    @Override
    public int bill_add(bill bill) {
        QueryRunner queryRunner = BaseDao.getQueryRunner();



        String sql="insert into smbms_bill(billCode,productName,productDesc,productUnit,productCount,totalPrice,isPayment,createdBy,creationDate,providerId) values(?,?,?,?,?,?,?,?,?,?)   ";
        int i=0;

        try {
            //查询一下供应商的id
            provider  provider =  queryRunner.query("select * from smbms_provider where proName= '" + bill.getproName()+"'", new BeanHandler<provider>(provider.class));
            i = queryRunner.update(sql, bill.getBillCode(), bill.getProductName(), bill.getProductDesc(), bill.getProductUnit(),
                    bill.getProductCount(), bill.getTotalPrice(), bill.getIsPayment(), bill.getCreatedBy(), bill.getCreationDate(), provider.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    @Override
    public int bill_delete(int id) {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String sql="delete from smbms_bill where id = "+id;
        int update=0;
        try {
           update = queryRunner.update(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return update;
    }

    @Override
    public bill bill_chakan(int id) {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        //用这个sql语句出错了，因为前端除了bill的字段还有provide的字段
//        String sql="select * from smbms_bill where id= "+id;
        String sql="select a.id,a.billCode,a.productName,a.productDesc ,a.productUnit,a.productCount,a.totalPrice,a.isPayment,a.createdBy,a.creationDate,a.modifyBy,a.modifyDate,a.providerId,b.proName from smbms_bill as a  left join smbms_provider as b "+
                "on  a.providerId=b.id where  a.id="+id;
        bill bill = null;
        try {
            bill = queryRunner.query(sql, new BeanHandler<>(bill.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return bill;
    }

    @Override
    public int bill_update(int id,bill bill) {
        QueryRunner queryRunner = BaseDao.getQueryRunner();
        String qq=bill.getproName();//获取供货商名字
        provider  providerld=null;
        try {
            //查询修改后的供货商id

            providerld=queryRunner.query("select id from smbms_provider where proName= '"+qq+"'", new BeanHandler<>(provider.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        System.out.println(providerld+"=="+qq);
        String sql="update smbms_bill set billCode ='"+bill.getBillCode()+"', productName= '"+bill.getProductName()+"', productUnit= '"+bill.getProductUnit()+"' ,productCount="+bill.getProductCount()+", totalPrice="+bill.getTotalPrice()+" ,providerId="+providerld.getId()+" , isPayment="+bill.getIsPayment()+"  where id="+id;
       int i=0;
        try {
        i= queryRunner.update(sql);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return i;
    }

    public static void main(String[] args) {
        billDaoimpl  b=new billDaoimpl();
        bill bills = b.bill_chakan(1);
        System.out.println(bills);

    }


}
